<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
ini_set('max_execution_time', '0');

/*******************
***  处理外部123数据库数据
***  http://192.168.1.100:89/fshop/
*** 2017/05/10
**********/
class AmazondataPro
{

	 public function __construct()
    {
        $this->CI =& get_instance();
        $this->db1 = $this->CI->load->database('default', TRUE);
        $this->db2 = $this->CI->load->database('amazondata', TRUE);
		
    }
	/*************读取123数据库newamazondb表中的数据到订单列表*********************/
	public function UpdateAmazondataPro()
	{
		/*
		$this->db1->select('*');
		$this->db1->from('db_order_list');
		$this->db1->join('d_shop', 'd_shop.id = db_order_list.shopid');
		$this->db1->where('order_status', 4);
		$this->db1->or_where('order_status ', 5);
		$this->db1->where('is_update_platform',0);
		$this->db1->where('d_shop.platfrom',2);
		$row = $this->db1->get()->result_array();*/
		$this->db1->update("db_order_list",array("is_update_platform"=>2),"shopid in(SELECT id from d_shop where country = 2 and platfrom = 2)  AND `order_source` ='正常订单' AND  is_update_platform = 0 and (`order_status` = 4 or order_status=5 )");//将日本的亚马逊店铺所有的未更新的订单更新成待查询状态
		//$this->db1->update("db_order_list",array("is_update_platform"=>0),"shopid in(SELECT id from d_shop where country = 2 and platfrom = 2) AND is_update_platform = 3");//将所有已更新有引受状态的订单更新成可更新到亚马逊
		$sql="SELECT * FROM `db_order_list` JOIN `d_shop` ON `d_shop`.`id` = `db_order_list`.`shopid` WHERE  (`order_status` = 4 or order_status=5 ) AND (`is_update_platform` =0 or `is_update_platform` =3)   AND `order_source` ='正常订单' AND `d_shop`.`platfrom` = 2 AND  `d_shop`.`amazonkey`!=''";
		$query = $this->db1->query($sql);
        $row = $query->result_array();
		foreach($row as $key=>$rs)
		{
			$this->db2->trans_begin();	//事务开始
			$rsdb = $this->db2->get_where("db_synorder"," orderid='".$rs["order_sn"]."'")->result_array();
			if(count($rsdb)==0)
			{
				$rsdbsell = $this->db1->get_where("db_order_sell","order_id='".$rs["order_id"]."'")->result_array();
				
				$rsdbamzonsell = $this->db2->get_one("SELECT * FROM db_amzordertmpsell A LEFT JOIN db_amzordertmp B ON A.ordertmpid = B.id WHERE B.amzorderid='".$rs["tradeid"]."' ");
			
				$post="";
				$post["orderid"] = $rs["order_sn"];
				$post["tradeid"] = $rs["tradeid"];
				$post["transactionid"] = $rsdbamzonsell["transactionid"];

				$post["ordernum"] = $rsdbsell[0]["goods_number"];

				$post["expressid"] = $rs["shipping_id"];
				$post["expresstypeid"] = $rs["shipping_number"];
				$post["expresstype"] = $rs["shipping_name"];

				//获取亚马逊支持的物流名

				$rsexp = $this->db1->get_one("SELECT * FROM db_order_express WHERE id = '".$rs["shipping_id"]."'");
				if($rsexp)
				{
					$post["nameen"] = $rsexp["amazon_express"];
					$post["filed16"] = $rsexp["amazon_express"];
				}else{
					$post["nameen"] = "Other";
					$post["filed16"] = "Other";
				}



				$post["filed18"] = '';

				$post["shoptypeid"] = $rs["shopname"];
				$post["shopid"] =$rs["shopid"];
				$post["syntime"] =date("Y-m-d H:i:s");
				$post["synflag"] =0;
				$this->db2->insert("db_synorder",$post);
				
				$this->db1->update("db_order_list",array("is_update_platform"=>1),"order_id=".$rs["order_id"]);

				if ($this->db2->trans_status() === FALSE)
				{
					$this->db2->trans_rollback();
				}
				else
				{
					$this->db2->trans_commit();
				}
			}
		}

		echo "OK";
		die;

		
	}
	
	/*******更新亚马逊产品信息**********/
	public function UpdateAmazonProductPro()
	{
		$sql="SELECT * FROM db_amazon_productlist where isupload = 1 and updateDate !='' ";
		$query = $this->db1->query($sql);
        $row = $query->result_array();
		if($row)
		{
			foreach($row as $key=>$rs)
			{
				
				//价格和库存单独进远程数据表


				$updateDate = unserialize($rs["updateDate"]);
			
		
				if($updateDate["new_price"])
				{
					$postprice="";
					$postprice["is_update"] = 0;
					$postprice["update_time"] = 0;
					$postprice["up_type"] = 1;
					$postprice["shopid"] = $rs["shopid"];
					$postprice["shopname"] = $rs["shopname"];
					$postprice["instime"] = date("Y-m-d H:i:s");
					$postprice["salesku"] = $rs["sales_sku"];
					$postprice["moneytype"] = $rs["moneytype"];
					$postprice["asin"] = $rs["product_id"];
					$postprice["updatestr"] = $updateDate["new_price"];//有值才更新
					$this->db2->insert("db_synorder_other",$postprice);
				}
				if($updateDate["new_qty"])
				{
					$postprice="";
					$postprice["is_update"] = 0;
					$postprice["update_time"] = 0;
					$postprice["shopid"] = $rs["shopid"];
					$postprice["up_type"] = 2;
					$postprice["shopname"] = $rs["shopname"];
					$postprice["instime"] = date("Y-m-d H:i:s");
					$postprice["salesku"] = $rs["sales_sku"];
					$postprice["moneytype"] = $rs["moneytype"];
					$postprice["asin"] = $rs["product_id"];
					$postprice["updatestr"] = $updateDate["new_qty"];//有值才更新
					$this->db2->insert("db_synorder_other",$postprice);
				}

				//更新到远程之后，清空本地数据
				$oldpost="";
				if($updateDate["new_price"]){
					$oldpost["original_price"] = $updateDate["new_price"];
					$oldpost["price"] = round($rs["moneyrate"]*$updateDate["new_price"],2);
				}
				if($updateDate["new_qty"]){
					$oldpost["qty"] = $updateDate["new_qty"];
				}
				$oldpost["isupload"] = 0;
				$oldpost["updateDate"] = '';

				$this->db1->update("db_amazon_productlist",$oldpost,"id='".$rs['id']."'");
		
			
			}
		}
	}
	public function checkAmazonOrder()
	{
		$starttime = time()-86400*2;
		$endtime = time()-86400*30;
		$row = $this->db2->select('tradeid')->get_where("db_amzordertmp","flag=2 AND fulfillmentchannel = 'MFN' AND status='Unshipped' and instime >='".$endtime."' AND instime <='".$starttime."' ")->result_array();
		foreach($row as $key=>$rs)
		{
			$rorder = $this->db1->select('order_id')->get_where("db_order_list","tradeid='".$rs["tradeid"]."'  ")->result_array();
			if(!$rorder)
			{
				$list[]=$rs["tradeid"];
				
			}
		}
		if(count($list)>0)
		{
			return $list;
		}
	}

	/*************读取123数据库newamazondb表中的数据到订单列表*********************/
	/********flag = 1 and ordersource = MFN *************/
	public function getinsertorderlist()
	{
	
		
		$factorycode = $this->factorycode();
		
		$this->db2->limit(1);
		$row = $this->db2->get_where("db_amzordertmp","flag=1 AND fulfillmentchannel = 'MFN' AND status='Unshipped' ")->result_array();
		if(count($row)==0)
		{
			return false;
		}
		$rs = $row[0];
		
	
			$rorder = $this->db1->get_where("db_order_list","tradeid='".$rs["tradeid"]."' ")->result_array();
			if($rorder[0]["order_id"])
			{
				$this->db2->update("db_amzordertmp",array("flag"=>2,"descr"=>'已同步系统成功1'),"tradeid='".$rs["tradeid"]."' ");
				return $this->getinsertorderlist();
			}
			
			$exchange=$this->db1->get_where("d_moneyrate","name='".$rs["moneytype"]."'")->result_array();
			$exchangerate=$exchange[0]["rate"]; 
			$postdata="";
			$postdata["order_sn"] = $this->getNo('amazon',$rs["tradeid"],$rs["shopid"]);
			$postdata["dates"] = date("Y-m-d");
			$postdata["order_status"] = 2;
			$postdata["order_source"] ='正常订单';
			$postdata["customerid"] =addslashes(trim($rs["custid"]));
			$postdata["consignee"] =addslashes(trim($rs["custname"]));
			if($rs["custcountry"]){
				$postdata["country"] = $rs["custcountry"];
				$rscountry = $this->db1->get_where("d_country","countrynamecn='".$rs["custcountry"]."'")->result_array();
				if($rscountry[0]["id"]){
					$postdata["country_cn"] = $rscountry[0]["countrynameen"];
				}

			}else{
				if($rs["countrycode"])
				{
					$rscountry = $this->db1->get_where("d_country","countryid='".trim($rs["countrycode"])."'")->result_array();
					$postdata["country"] = strtolower($rscountry[0]['countrynamecn']);
					$postdata["country_cn"] = $rscountry[0]["countrynameen"];
				}
			}
			if($rs["countrycode"]=="")
			{
				$rscountry = $this->db1->get_where("d_country","countrynamecn='".trim($rs["custcountry"])."'")->result_array();
				$country_code = $rscountry[0]['countryid'];
			}else{
				$country_code = $rs["countrycode"];
			}
			$postdata["country_code"] = $country_code;

			$postdata["province"] = $rs["custprovince"];
			$postdata["city"] = $rs["custcity"];
			$postdata["address"] = addslashes(trim($rs["custaddress1"]));
			$postdata["address2"] = addslashes(trim($rs["custaddress2"]));
			$postdata["zipcode"] = $rs["custzipcode"]?$rs["custzipcode"]:0;
			$postdata["tel"] = $rs["custtel1"]?$rs["custtel1"]:0;
			$postdata["mobile"] = $rs["custtel2"];
			$postdata["email"] = $rs["custemail"];
			$postdata["shopid"] = $rs["shopid"];
			$rshop = $this->db1->get_where("d_shop","id=".$rs["shopid"])->result_array();
			$postdata["shopname"] = $rshop[0]["name"];
			$postdata["shopoper"] = $rshop[0]["updateoper"];
			$postdata["moneytype"] = $rs["moneytype"];
			$postdata["tradeid"] = $rs["tradeid"];
			$postdata['platformID']  = $rshop[0]['platfrom'];//平台
			

			
			$postdata["goods_originask"] = $rs["origintotle"];	//原金额这个金额包含运费
			$exchange=$this->db1->get_where("d_moneyrate","name='".$rs["moneytype"]."'")->result_array();
			$exchangerate=$exchange[0]["rate"]; 
			$postdata['moneyrate']  = $exchangerate;//汇率
			$orderask = $rs["origintotle"] * $exchangerate;
			$originexpressask = $rs["originexpressask"]* $exchangerate;
			$postdata["goods_amount"] =$orderask;
			$postdata["shipping_fee"] = $rs["originexpressask"]* $exchangerate;
			$postdata["shipping_originask"] = $rs["originexpressask"];
			$postdata["add_time"] = time();
			$postdata["ordertime"] = $rs["ordercreatetime"];
			$postdata["saleschannel"] = $rs["saleschannel"];
			
			if($rshop[0]["shop_platform_fee"]>0)
			{
				$shop_platform_fee  =$rshop[0]["shop_platform_fee"];
			}else{
				$shop_platform_fee = 0.15;
			}
			if($rshop[0]["shop_pay_fee"]>0)
			{
				$shop_pay_fee  =$rshop[0]["shop_pay_fee"];
			}else{
				$shop_pay_fee = 0.015;
			}


			$postdata["pay_fee"] = ($orderask+$originexpressask)*$shop_pay_fee;
			$postdata["platform_fee"] = ($orderask+$originexpressask)*$shop_platform_fee;
		
		
			$this->db1->trans_begin();	//事务开始
			
				$this->db1->insert("db_order_list",$postdata);
				$orderID = $this->db1->insert_id();
				$sell = $this->db2->get_where("db_amzordertmpsell","ordertmpid='".$rs["id"]."'")->result_array();
				if(count($sell)==0)	//没有抓取到SKU
				{
					$this->db1->update("db_order_list",array("order_status"=>7),"order_id=".$orderID);
				}
				$weight = 0;
				$allshipprice=0;
				$origintotle = 0;
				foreach($sell as $key=>$res)
				{
					
					

					$allshipprice =$allshipprice+$res['shipprice'];	//运费
					$origintotle = $origintotle+$res['sellprice']*$res["ordernum"];	//商品金额
					$oldgoodsku = $res["productid"];
					if(strpos($oldgoodsku,"@") === false){  
						$oldgoodsku = $res["productid"];
					}else{
						$oldgoodsku = explode("@",$res["productid"]);
						$oldgoodsku = $oldgoodsku[0];
					}
					/*****************替换SKU方式********
						**  1、是否存在123.fshop数据库d_aotocreatesku表中
						**  2、是否存在在商品的db_goods 中的cognatelist字段
						**  3、是否db_salesku中
					*********************************/
					$productid = "";
					$newgoods = $this->db2->get_where("fshop.d_aotocreatesku","autosku='".$oldgoodsku."' AND shopname='".$rshop[0]["name"]."' ")->result_array();	//原销售SKU是否存在
					
					if(count($newgoods)>0)
					{
						$productid = $newgoods[0]["parentsku"];
						$newgoods = $this->db1->get_where("db_goods","goods_sku='".$productid."' or cognatelist like '%,".$productid.",%' ")->result_array(); //找SKU是否存在
						if(count($newgoods)>0)
						{
							$productid = $newgoods[0]["goods_sku"];
						}
						
					}else{
						$newgoods = $this->db1->get_where("db_goods","goods_sku='".$oldgoodsku."' ")->result_array(); //找SKU是否存在
						if(count($newgoods)>0)
						{
							$productid = $newgoods[0]["goods_sku"];
						}else{

							$newgoods = $this->db1->get_where("db_goods","  cognatelist like '%,".$oldgoodsku.",%'")->result_array();	//子SKU是否存在
							if(count($newgoods)>0)
							{
								$productid = $newgoods[0]["goods_sku"];
							}else{
								$newgoods = $this->db1->get_where("db_salesku","autosku='".$oldgoodsku."'")->result_array();//这里一定要对应goods_id信息,不能使用goods_sku
								if(count($newgoods)>0)
								{
									$goods_id = $newgoods[0]["goods_id"];
									$rowrsdb = $this->db1->get_where("db_goods","goods_id='".$goods_id."'")->result_array();
									if(count($rowrsdb)>0)
									{
										$productid = $rowrsdb[0]["goods_sku"];
									}else{
										$productid = $oldgoodsku;
									}
									
								}
							}


							
						
						}
					}
					if(!$productid)
					{
						$productid = $oldgoodsku;
					}

					$rgoods = $this->db1->get_where("db_goods","goods_sku='".$productid."'")->result_array();
					if($rgoods[0]["goods_id"])
					{
						$goods_id = $rgoods[0]["goods_id"];
						$sweight = $res["ordernum"] * $rgoods[0]["g_weight"];
						$weight = $weight+$sweight;
						$goods_name =  $rgoods[0]["goods_name"];
						/*****************成本价计算方式********
						**  11、12开头定制的衣服按成本价计算
						**  批量的sku 按关联价计算
						**  外采购的衣服按成本价计算
						*********************************/
						$costprice = 0;
						if(is_numeric($productid) && preg_match('|^\d{8}$|',$productid))	//数字并且8位SKU并且11 12开头
						{
							$goodscode  = substr($productid,0,2);//取开头2位数
							if(in_array($goodscode,$factorycode))	//是否存在在工厂code
							{
								$costprice = $rgoods[0]["g_costprice"];	//定制衣服按成本价计算
							}
						}else{
							//如果当前SKU不是8位，查询父SKU是不是8位并且11，12开头
							if(is_numeric($rgoods[0]["goods_parent_sku"]) && preg_match('|^\d{8}$|',$rgoods[0]["goods_parent_sku"]))	//数字并且8位SKU并且11 12开头
							{
								$goodscodes  = substr($rgoods[0]["goods_parent_sku"],0,2);//取开头2位数
								if(in_array($goodscodes,$factorycode))	//是否存在在工厂code
								{
									if($rgoods[0]["bulkprice"]>0)
									{
										$costprice =  $rgoods[0]["bulkprice"];	//批量按关联价计算
									}else{
										$costprice =$rgoods[0]["g_costprice"];	//定制衣服按成本价计算
									}
								}else{
									$costprice =$rgoods[0]["g_costprice"];	//定制衣服按成本价计算
								}
							}else{
								$costprice = $rgoods[0]["g_costprice"];
							}
							
						}
						
					}else{
						$goods_id = 0 ;
						$costprice = 0;
						$goods_name="";
					}

					$amount = $res["ordernum"] * $res["sellprice"];
					$postsell = "";
					$postsell["order_id"] = $orderID;
					$postsell["goods_id"] = $goods_id;
					$postsell["goods_name"] = $goods_name?$goods_name:addslashes($res["productname"]);
					$postsell["goods_sn"] = $productid;
					$postsell["costprice"] = $costprice;
					$postsell["goods_number"] = $res["ordernum"];
					$postsell["goods_price"] = $res["sellprice"]*$exchangerate;
					$postsell["goods_weight"] = $rgoods[0]["g_weight"]?$rgoods[0]["g_weight"]:0;
					$postsell["goods_amount"] = $amount*$exchangerate;
					$postsell["originsellprice"] = $res["sellprice"];
					$postsell["originsku"] = $oldgoodsku;
					$this->db1->insert("db_order_sell",$postsell);
					
					if($goods_id==0)
					{
						$this->db1->update("db_order_list",array("order_status"=>7),"order_id=".$orderID);
					}
					if($res["ordernum"]==0)
					{
						$this->db1->update("db_order_list",array("order_status"=>7),"order_id=".$orderID);
					}
				}
				if($weight)
				{
					$this->db1->update("db_order_list",array("orderweight"=>$weight),"order_id=".$orderID);
				}
				if($allshipprice)
				{
					$shipping_fee =round($allshipprice * $exchangerate,2);
					$this->db1->update("db_order_list",array("shipping_originask"=>$allshipprice,"shipping_fee"=>$shipping_fee),"order_id=".$orderID);
				}
				if($origintotle)
				{
					$orderask = round($origintotle * $exchangerate,2);
					$pay_fee = round($orderask*$shop_pay_fee,2);
					$platform_fee = round($orderask*$shop_platform_fee,2);
					$this->db1->update("db_order_list",array("goods_originask"=>$origintotle,"goods_amount"=>$orderask,"pay_fee"=>$pay_fee,"platform_fee"=>$platform_fee),"order_id=".$orderID);
				}
				//更新日志
				$imgdata["goods_id"] = $orderID;	//订单ID
				$imgdata["updatetime"] = date("Y-m-d H:i:s");
				$imgdata["createUid"] = 0;
				$imgdata["types"] = 3;
				$imgdata["content"] = 'Amazon新系统接口自动新增订单,交易ID='.$rs["tradeid"];
				$imgdata["createUser"] ='auto';
				$this->db1->query($this->db1->insert_string('db_goods_log', $imgdata));
				
				$this->db2->update("db_amzordertmp",array("flag"=>2,"descr"=>'已同步系统成功'.$orderID),"tradeid='".$rs["tradeid"]."' AND id ='".$rs["id"]."'");

				if ($this->db1->trans_status() === FALSE)
				{
					$this->db1->trans_rollback();
				}
				else
				{
					$this->db1->trans_commit();
				}


		
			
			return $this->getinsertorderlist();
		
	}

	public  function getNo($shopName='',$tradeid='',$shopid='') {
		
		if($shopName)
		{
			if($shopName =='wish')
			{
				 $randCode = $this->randCode(5,1);
				 $ors=$randCode.substr(time(),-5).substr(microtime(),2,5);     //生成16位数字基本号
			}
			if($shopName =='amazon')
			{
				if($tradeid)
				{
					$no = str_replace("-","",$tradeid);
					$ors = $shopid.$no;
				}else{
					 $randCode = $this->randCode(5,1);
					 $ors=$randCode.substr(time(),-5).substr(microtime(),2,5);     //生成16位数字基本号
				}
			}
			if($shopName =='ebay')
			{
				if($tradeid)
				{
					$no = str_replace("-","",$tradeid);
					$ors = $no;
					}else{
					 $randCode = $this->randCode(5,1);
					 $ors=$randCode.substr(time(),-5).substr(microtime(),2,5);     //生成16位数字基本号
				}
			}
		
		}else{
			$randCode = $this->randCode(5,1);
			$ors=$randCode.substr(time(),-5).substr(microtime(),2,5);     //生成16位数字基本号
			/*
			$zigui = "FM";
			$sql = "select max(right( order_sn, 12 ) *1) as hot from db_order_list where dates='" . date("Y-m-d") . "' group by dates ";
			$query = $this->db1->query($sql);
			$hot = $query->result_array();
			if ($hot[0]['hot']) {
				$no = substr($hot[0]['hot'], 8, strlen($hot[0]['hot']));
				$no = $no + 10001;
				$no = date("Ymd") . substr($no, 1, strlen($no));

				$ors =$zigui . $no;
			}
			else
				$ors =$zigui . date("Ymd") . "0001";*/
		}
		$order  = $this->db1->get_where("db_order_list","order_sn ='".$ors."'")->result_array();
		if(count($order)>0)	//如果存在，则自动生成一个无序订单号
		{
			return $this->getNo();
		}else{
			return $ors;
		}

    }
	
	/**
	  +----------------------------------------------------------
	 * 生成随机字符串
	  +----------------------------------------------------------
	 * @param int       $length  要生成的随机字符串长度
	 * @param string    $type    随机码类型：0，数字+大小写字母；1，数字；2，小写字母；3，大写字母；4，特殊字符；-1，数字+大小写字母+特殊字符
	  +----------------------------------------------------------
	 * @return string
	  +----------------------------------------------------------
	 */
	function randCode($length, $type = 0) {
		if($length ==999)
		{
			$length=mt_rand(3,10);
		}
		$arr = array(1 => "123456789", 2 => "abcdefghijklmnopqrstuvwxyz", 3 => "ABCDEFGHIJKLMNOPQRSTUVWXYZ");
		if ($type == 0) {
			array_pop($arr);
			$string = implode("", $arr);
		} elseif ($type == "-1") {
			$string = implode("", $arr);
		} else {
			$string = $arr[$type];
		}
		$count = strlen($string) - 1;
		$code = '';
		for ($i = 0; $i < $length; $i++) {
			$code .= $string[rand(0, $count)];
		}
		return $code;
	}


	public function factorycode()
	{
		$rsdb = $this->db1->get_where("db_factory","code >0")->result_array();
		foreach($rsdb as $key =>$rs)
		{
			$factorycode[] = $rs["code"];	//工厂编号
		}

		return $factorycode;
	}
	
	public function savezangzhuorder()
	{
		$orderlist = $this->CI->fun->getdatabaseslist("db_order_list A left join db_order_express B on A.shipping_id = B.id"," AND shopid= 72 and order_status= 4  AND email !=''"," A.*,B.amazon_express");
			if(count($orderlist))
			{
				$post = "";
				$up = 0;
				foreach($orderlist as $key=>$rs)
				{
					$rsdb =$this->db2->get_one("SELECT * from db_magento_sponsor where order_sn='".$rs['order_sn']."' ");
					if(!$rsdb){
						$up = 1;
						$post[$key]["order_sn"] = $rs["order_sn"];
						$post[$key]["customerid"] = $rs["customerid"];
						$post[$key]["consignee"] = $rs["consignee"];
						$post[$key]["country"] = $rs["country"];
						$post[$key]["province"] = $rs["province"];
						$post[$key]["city"] = $rs["city"];
						$post[$key]["address"] = $rs["address"].$rs["address2"];
						$post[$key]["email"] = $rs["email"];
						$post[$key]["zipcode"] = $rs["zipcode"];
						$post[$key]["shipping_name"] = $rs["amazon_express"]?$rs["amazon_express"]:"Other";
						$post[$key]["shipping_number"] = $rs["shipping_number"];
						$sell = $this->db1->get_one("SELECT GROUP_CONCAT(goods_sn) as goods_sn,GROUP_CONCAT(goods_number) as goods_number from db_order_sell where order_id='".$rs['order_id']."' ");
						$post[$key]["goods_sn"] = $sell["goods_sn"];
						$post[$key]["goods_num"] = $sell["goods_number"];
					}
					
				}
				if($up==1){
					$this->db2->insert_batch("db_magento_sponsor",$post);
				}
			}
	}
	
	
}